ALTER PROCEDURE sp_CK40N_Report 
AS
BEGIN
IF  EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects s WHERE s.id=OBJECT_ID('tempdb..#T_CK40N')) 
BEGIN
  DROP TABLE #T_CK40N
END

DELETE sc  FROM   SAP_CK40N sc WHERE sc.Message_Type='I'

CREATE TABLE  #T_CK40N(
	[Exception] [nvarchar](50) NULL,
	[Message_Type] [nvarchar](50) NULL,
	[Material] [nvarchar](20) NULL,
	[Plant] [nvarchar](100) NULL,
	[Application_Area] [nvarchar](100) NULL,
	[Message] [nvarchar](100) NULL,
	[Message_Text] [nvarchar](500) NULL,
	[Item_Number] [nvarchar](20) NULL,
	[Oid] [uniqueidentifier] NOT NULL
	,[ReportInfo] [NVARCHAR](200),
	[SubComponent] [NVARCHAR](200)
	 )

INSERT INTO #T_CK40N
(
	Exception,
	Message_Type,
	Material,
	Plant,
	Application_Area,
	[Message],
	Message_Text,
	Item_Number,
	Oid 
)
SELECT Exception,
	Message_Type,
	Material,
	Plant,
	Application_Area,
	[Message],
	Message_Text,
	Item_Number,
	Oid 
FROM  SAP_CK40N sc

 UPDATE x SET x.ReportInfo =    x.Material+'-'+ REPLACE(  REPLACE( x.Message_Text,'Cost estimate for material','<-'),'/ plant CN02 is incorrect','')
    ,x.SubComponent= REPLACE(  REPLACE( x.Message_Text,'Cost estimate for material ',''),'/ plant CN02 is incorrect','')
  FROM #T_CK40N  x


;

 
SELECT    tc.Material, tc.[Message],
tc.Message_Text, tc.ReportInfo, tc.SubComponent
 
FROM #T_CK40N tc
ORDER BY tc.Material

DECLARE @T_Rst AS TABLE (Material NVARCHAR(200))


INSERT INTO @T_Rst
(
	Material
)
SELECT sc.Material FROM SAP_CK40N sc


IF EXISTS(SELECT 1  FROM tempdb.dbo.sysobjects s WHERE s.id=OBJECT_ID('tempdb..#T_Rst'))
BEGIN
	DROP TABLE #T_Rst
END

CREATE TABLE #T_Rst (Material NVARCHAR(20),Msg NVARCHAR(500),Monitored NVARCHAR(20),[Status of Extending] NVARCHAR(20),[Prod.Level] NVARCHAR(200))



INSERT INTO #T_Rst
(
	Material,
	Msg,
	Monitored,
	[Status of Extending],
	[Prod.Level]
)
SELECT  DISTINCT
x.Material,
Msg= ( SELECT  DISTINCT

   CASE sc.[Message]WHEN '476' THEN ' NO Routing '
                    WHEN '424' THEN  ' NO BOM '
                    WHEN '060' THEN  ''
                    WHEN '240' THEN  ''
                    WHEN '322' THEN  ''
                    WHEN '318' THEN  '318 from Sub-Component Issues -No Routing or No BOM'
                    WHEN '168' THEN  '168 from Sub-Component Issues'
                    WHEN '465' THEN  sc.Message_Text
                   ELSE  
 
  sc.[Message]+'(' +sc.Message_Text+')/' END
 FROM SAP_CK40N sc

             WHERE sc.Material=x.Material FOR XML PATH(''))
,[Mornitored]=CASE isnull(sim.Material_Number,'') WHEN '' THEN 'Not be Tracking' ELSE 'Y' END
,[Status of Extending]=isnull(sim.Extending_Status,'Not be Tracking')
,[Prod.Level]=case isnull(scl.Material,'') WHEN '' THEN 'Sub-Component' ELSE 'Top Level' END 
 FROM @T_Rst x
 LEFT JOIN SAP_ItemMaster sim ON x.Material=sim.Material_Number AND isnull(sim.ActiveStatus,'')!='X'
 LEFT JOIN SAP_CK40N_List scl ON scl.Material=x.Material
 
 
 
 --SELECT scl.Material,x.* FROM  SAP_CK40N_List scl 
 --LEFT JOIN #T_Rst x ON x.Material = scl.Material COLLATE Chinese_PRC_90_CS_AI
 --ORDER BY x.Material

--SELECT   scl.Material, tc.Material, tc.[Message],
--       tc.Message_Text, tc.ReportInfo, tc.SubComponent
--       ,[ParentMaterial]=tp.Material
--FROM SAP_CK40N_List scl 
--LEFT JOIN #T_CK40N tc ON tc.Material=scl.Material COLLATE Chinese_PRC_90_CS_AI AND tc.[Message]='168'
--LEFT JOIN  #T_CK40N tp ON tp.SubComponent=tc.Material
--ORDER BY tc.[Message]

SELECT SeqIdx=ROW_NUMBER() OVER (  ORDER BY  v.Msg   ), v.Material, v.Material_Description,Message_Text,   
       v.MRP_controller ,v.Msg,v.Monitored,v.[Status of Extending],v.[Prod.Level]
 
FROM  
(
SELECT DISTINCT scl.Material,sm.Material_Description
 
 
,Message_Text=isnull(rst.Msg,'Can be released')
,sc.MRP_controller
,Msg=ISNULL( rst.Msg,'-'),Monitored=ISNULL( rst.Monitored,'-'),[Status of Extending]=ISNULL( rst.[Status of Extending],'-')
,[Prod.Level]=ISNULL( rst.[Prod.Level],'-')
FROM  SAP_CK40N_List scl
LEFT JOIN SAP_CK40N  x ON scl.Material =x.Material COLLATE Chinese_PRC_90_CI_AS    
LEFT JOIN SAP_MARA sm ON sm.Material=scl.Material COLLATE Chinese_PRC_90_CS_AI
LEFT JOIN SAP_MARC sc ON sc.Material=scl.Material
LEFT JOIN SAP_ItemMaster sim ON sim.Material_Number=scl.Material AND isnull(sim.ActiveStatus,'')!='X'
LEFT JOIN #T_Rst rst ON rst.Material=scl.Material COLLATE Chinese_PRC_90_CS_AI
 
) v ORDER BY   v.Msg  

SELECT tc.Material,tc.Message_Text,sm.Material_Description FROM #T_CK40N tc
LEFT JOIN SAP_MARA sm ON sm.Material=tc.Material COLLATE Chinese_PRC_90_CS_AI
WHERE   EXISTS (
  SELECT 1 FROM SAP_ItemMaster sim WHERE sim.Material_Number=tc.Material COLLATE Chinese_PRC_90_CS_AI	
)
AND tc.[Message]='465'

END